﻿using Chire.ChireInter.Students;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Chire.ChireInter.Parent
{
    public class Parent_Action
    {
        #region 根据家长openId获取家长信息
        public Parent_Model getParentWithopenId(string openId)
        {
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();

            string strselect = "select top 1 * from parent where openid = '" + openId + "'";
            SqlCommand sqlcmd = new SqlCommand(strselect, sqlcon);
            SqlDataReader dr = sqlcmd.ExecuteReader();
            Parent_Model parentModel = new Parent_Model();
            if (dr.Read())
            {
                parentModel.id = dr["id"].ToString();
            }
            sqlcon.Close();
            return parentModel;
        }
        #endregion

        #region 根据家长id进行绑定
        public void bindingParentWithAuth(string name, string openid, string binding) {
            // 连接数据库
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();
            string StrInsert = "";

            StrInsert = "insert into parent(name,phone,binding,datetime,openid) values (@name,@phone,@binding,@datetime,@openid)";
            SqlCommand cmd = new SqlCommand(StrInsert, sqlcon);
            // 添加参数并且设置参数值

            // 1. 作者id
            cmd.Parameters.Add("@name", SqlDbType.VarChar, 50);
            cmd.Parameters["@name"].Value = name;
            // 1. 日记标题
            cmd.Parameters.Add("@phone", SqlDbType.VarChar, 50);
            cmd.Parameters["@phone"].Value = "";
            // 2.日记详情
            cmd.Parameters.Add("@binding", SqlDbType.VarChar, 50);
            cmd.Parameters["@binding"].Value = binding;

            cmd.Parameters.Add("@openid", SqlDbType.VarChar, 50);
            cmd.Parameters["@openid"].Value = openid;

            cmd.Parameters.Add("@datetime", SqlDbType.Float, 50);
            cmd.Parameters["@datetime"].Value = DateTime.Now.ToString();

            // 执行插入数据的操作
            cmd.ExecuteNonQuery();
            sqlcon.Close();
        }
        #endregion

        #region 插入家长信息
        public void authBindingParentInfo(string parentOpenId, string studentName, string studentOpenId)
        {

            // 连接数据库
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();
            string StrInsert = "";
            StrInsert = "insert into parent(id,name,phone,binding,datetime,openid) values(@id,@name,@phone,@binding,@datetime,@openid)";
            SqlCommand cmd = new SqlCommand(StrInsert, sqlcon);
            // 添加参数并且设置参数值
            // 1. 认证类型
            cmd.Parameters.Add("@id", SqlDbType.VarChar, 50);
            cmd.Parameters["@id"].Value = parentOpenId;
            // 2. 随机数
            cmd.Parameters.Add("@name", SqlDbType.VarChar, 50);
            cmd.Parameters["@name"].Value = studentName + "家长";
            // 3. 认证openid
            cmd.Parameters.Add("@phone", SqlDbType.VarChar, 50);
            cmd.Parameters["@phone"].Value = "";

            cmd.Parameters.Add("@binding", SqlDbType.VarChar, 50);
            cmd.Parameters["@binding"].Value = studentOpenId;
            // 4.认证时间
            cmd.Parameters.Add("@datetime", SqlDbType.VarChar, 100);
            cmd.Parameters["@datetime"].Value = DateTime.Now.ToString(); ;

            // 4.认证时间
            cmd.Parameters.Add("@openid", SqlDbType.VarChar, 100);
            cmd.Parameters["@openid"].Value = parentOpenId;

            // 执行插入数据的操作
            cmd.ExecuteNonQuery();
            sqlcon.Close();
        }
        #endregion

        #region 根据学生id获取所有家长

        public List<Parent_Model> getAllParentWithStudentId(string studentId) {

            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();

            string strselect = "select * from parent where binding = '"+studentId+"' order by id desc";
            //查询音乐信息
            DataTable dt = new DataTable();

            SqlDataAdapter dap = new SqlDataAdapter(strselect, sqlcon);
            dap.Fill(dt);
            int rows = dt.Rows.Count;

            List<Parent_Model> parentList = new List<Parent_Model>();
            for (int i = 0; i < rows; i++)
            {
                string id = dt.Rows[i]["id"].ToString();
                string name = dt.Rows[i]["name"].ToString();
                string phone = dt.Rows[i]["phone"].ToString();
                string binding = dt.Rows[i]["binding"].ToString();
                string datetime = dt.Rows[i]["datetime"].ToString();
                string openid = dt.Rows[i]["openid"].ToString();

                Parent_Model parentModel = new Parent_Model()
                {
                    id = id,
                    name = name,
                   phone = phone,
                   binding = binding,
                   datetime = datetime,
                   openid = openid,
                };
                parentList.Add(parentModel);
            }
            sqlcon.Close();
            return parentList;
        }
        #endregion

        #region 获取所有的家长
        public List<Parent_Model> getAllParent()
        {

            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();

            string strselect = "select * from parent";
            //查询音乐信息
            DataTable dt = new DataTable();

            SqlDataAdapter dap = new SqlDataAdapter(strselect, sqlcon);
            dap.Fill(dt);
            int rows = dt.Rows.Count;

            List<Parent_Model> parentList = new List<Parent_Model>();
            for (int i = 0; i < rows; i++)
            {
                string id = dt.Rows[i]["id"].ToString();
                string name = dt.Rows[i]["name"].ToString();
                string phone = dt.Rows[i]["phone"].ToString();
                string binding = dt.Rows[i]["binding"].ToString();
                string datetime = dt.Rows[i]["datetime"].ToString();
                string openid = dt.Rows[i]["openid"].ToString();

                Parent_Model parentModel = new Parent_Model()
                {
                    id = id,
                    name = name,
                    phone = phone,
                    binding = binding,
                    datetime = datetime,
                    openid = openid,
                };
                parentList.Add(parentModel);
            }
            sqlcon.Close();
            return parentList; 
        }
        #endregion

        #region 根据家长openId 获取学生openId
        public Student_Model getStudentIdWithBindingParentId(string parentOpenId) {
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();

            string strselect = "select top 1 * from parent where openid = '" + parentOpenId + "'";
            SqlCommand sqlcmd = new SqlCommand(strselect, sqlcon);
            SqlDataReader dr = sqlcmd.ExecuteReader();
            string studentId = "";
            if (dr.Read())
            {
                studentId = dr["binding"].ToString();
            }
            sqlcon.Close();
            
            Student_Action studentAction = new Student_Action();
            Student_Model studentModel = studentAction.getStudentInfoWithId(studentId);
            return studentModel;
        }
        #endregion
    }
}